import pandas as pd
import timeit
import tstables
import tables
from datetime import *
import codecs
import time
import os

# print(data.head())
# h5w = pd.HDFStore('stocks_1d.h5', 'r', complevel=9, complib='blosc')

# # t1 = timeit.Timer('单支股票')
# # print(h5w['stock_600010'])
# stock = h5w['stock_600010']
# # stock.select('date_time', whrere="date_time>2017-06-06", iterator=True)
# h5w.select("/stock_600010", where=['date_time<Timestamp("2015-01-07")'])
# # t1.timeit()
# # print(slice)
# stock.close()
# h5w.close()

# df = pd.read_hdf('stocks_1d.h5', key="/stock_600010", where="date_time<Timestamp('2015-01-07')")
#
# print(df)
# #write
# store=pd.HDFStore("./data/Minutes.h5","a", complevel=9, complib='zlib')
# store.put("Year2015", dfMinutes, format="table", append=True, data_columns=['dt','code'])
# # read
# store=pd.HDFStore("./data/Minutes.h5","r")
# store.select("Year2015", where=['dt<Timestamp("2015-01-07")','code=="000570"'])


# df = pd.read_csv(
#     'H:/股票数据/2017年逐笔数据/201709/20170911/000/000002.csv',
#     skiprows=4,
#     skipfooter=0,
#     parse_dates=True,
#     index_col=0,
#     encoding='gbk',
#     engine='python',
#     names=['time', 'volume', 'price', 'type'])
#
# # print(df)
#
# print(df.sum())


# Class to use as the table description
class Transaction(tables.IsDescription):
    # timestamp = tables.Int64Col(pos=0)
    # volume = tables.Int64Col(pos=1)
    # price = tables.Float64Col(pos=2)
    # isBuy = tables.BoolCol(pos=3)
    timestamp = tables.Int64Col(pos=0)
    open = tables.Float64Col(pos=1)
    closeYesterday = tables.Float64Col(pos=2)
    current = tables.Float64Col(pos=3)
    high = tables.Float64Col(pos=4)
    low = tables.Float64Col(pos=5)
    ask = tables.Float64Col(pos=6)
    bid = tables.Float64Col(pos=7)
    volume = tables.Int64Col(pos=8)
    turnover = tables.Float64Col(pos=9)
    ask1Volume = tables.Int64Col(pos=10)
    ask1Price = tables.Float64Col(pos=11)
    ask2Volume = tables.Int64Col(pos=12)
    ask2Price = tables.Float64Col(pos=13)
    ask3Volume = tables.Int64Col(pos=14)
    ask3Price = tables.Float64Col(pos=15)
    ask4Volume = tables.Int64Col(pos=16)
    ask4Price = tables.Float64Col(pos=17)
    ask5Volume = tables.Int64Col(pos=18)
    ask5Price = tables.Float64Col(pos=19)
    bid1Volume = tables.Int64Col(pos=20)
    bid1Price = tables.Float64Col(pos=21)
    bid2Volume = tables.Int64Col(pos=22)
    bid2Price = tables.Float64Col(pos=23)
    bid3Volume = tables.Int64Col(pos=24)
    bid3Price = tables.Float64Col(pos=25)
    bid4Volume = tables.Int64Col(pos=26)
    bid4Price = tables.Float64Col(pos=27)
    bid5Volume = tables.Int64Col(pos=28)
    bid5Price = tables.Float64Col(pos=29)
    type = tables.Int64Col(pos=30)


# Use pandas to read in the CSV data
def date_parser(dates):
    return time.strftime("%Y-%m-%d %H:%M:%S",
                         time.gmtime(time.mktime(time.strptime(dates, "%Y-%m-%d %H:%M:%S"))))


path = '../etl/stocks'

f = tables.open_file('time_series.h5', 'a', )

for dirpath, dirnames, filenames in os.walk(path):
    for file in filenames:
        fullpath = os.path.join(dirpath, file)

        tspd = pd.read_csv(
            fullpath,
            # delimiter=',',
            # skipinitialspace=True,
            skiprows=1,
            skipfooter=None,
            parse_dates=True,  # {'datetime': [29, 30]},
            index_col=0,
            date_parser=date_parser,
            # encoding='utf8',
            converters={
                'open': float,
                'closeYesterday': float,
                'current': float,
                'high': float,
                'low': float,
                'ask': float,
                'bid': float,
                'volume': int,
                'turnover': float,
                'ask1Volume': int,
                'ask1Price': float,
                'ask2Volume': int,
                'ask2Price': float,
                'ask3Volume': int,
                'ask3Price': float,
                'ask4Volume': int,
                'ask4Price': float,
                'ask5Volume': int,
                'ask5Price': float,
                'bid1Volume': int,
                'bid1Price': float,
                'bid2Volume': int,
                'bid2Price': float,
                'bid3Volume': int,
                'bid3Price': float,
                'bid4Volume': int,
                'bid4Price': float,
                'bid5Volume': int,
                'bid5Price': float,
                'type': int,
            },
            # engine='python',
            nrows=1000,
            names=['timestamp',
                   'open',
                   'closeYesterday',
                   'current',
                   'high',
                   'low',
                   'ask',
                   'bid',
                   'volume',
                   'turnover',
                   'ask1Volume',
                   'ask1Price',
                   'ask2Volume',
                   'ask2Price',
                   'ask3Volume',
                   'ask3Price',
                   'ask4Volume',
                   'ask4Price',
                   'ask5Volume',
                   'ask5Price',
                   'bid1Volume',
                   'bid1Price',
                   'bid2Volume',
                   'bid2Price',
                   'bid3Volume',
                   'bid3Price',
                   'bid4Volume',
                   'bid4Price',
                   'bid5Volume',
                   'bid5Price',
                   'type']
        )

        tspd = tspd.sort_index(axis=0, ascending=True)

        fn = file.replace('.csv', '')
        # Create a new time series
        ts = f.create_ts('/', fn, Transaction)

        # Append the BPI data
        ts.append(tspd)

f.close()
# Read in some data
# read_start_dt = datetime(2014,1,4,12,00)
# read_end_dt = datetime(2014,1,4,14,30)
#
# rows = ts.read_range(read_start_dt,read_end_dt)

# `rows` will be a pandas DataFrame with a DatetimeIndex.

#  time awk -F"," '{print $32" "$33","$3","$4","$6","$7","$8","$9","$10","$11","$12","$13","$14","$15","$16","$17","$18","$19","$20","$21","$22","$23","$24","$25","$26","$27","$28","$29","$30","$31}' 2017-09-250.csv > 2017-09-25.csv
